//TODO：存在问题————插入内容中含单引号无法正常添加
const mysql = require('mysql')
const util = require("./utils/index")
const db = mysql.createPool({
    host: '127.0.0.1',
    user: 'root',
    password: 'qaz445484430',
    database: 'runblog',
    port: 3306,
    connectionLimit: 50,//允许连接数
    multipleStatements: true,  //是否允许执行多条sql语句
    timezone: "08:00" //大坑，必须加这一句，否则时间不对劲
})


//封装数据库sql请求操作，返回的是一个包含对象的数组
let Query = (sql, ...params) => {
    return new Promise(function (resolve, reject) {
        db.query(sql, params, function (error, res) {
            if (error) {
                reject(error);
            }
            resolve(res);
        });
    });
};

//获取用户信息列表
let getUserInfoList = function () {
    let sql = `
        select
            id,
            name,
            imgpath,
            type,
            regtime
        from
            user
        where
            isdelete=0
        `;
    return sql;
}

//获取个人信息详情
let getUserDetailByName = function (name) {
    let sql = `
        select
            *
        from
            user
        where
            isdelete=0 and name='${name}'
        `;
    return sql;
}

//获取个人信息详情
let getUserDetailById = function (id) {
    let sql = `
        select
            *
        from
            user
        where
            isdelete=0 and name='${id}'
        `;
    return sql;
}

//注册用户
let regNewUser = function (name, password) {
    let sql = `
        insert into 
            user (name,password,regdate) 
        values 
            ('${name}','${password}',NOW());
    `;
    return sql;
}

//删除用户
let deleteUser = function (id) {
    let sql = `
    UPDATE
        user
    SET
        isdelete=1
    where
        id='${id}'
        `;
    return sql;
}

//修改密码
let changeUserPassword = function (id, newPassword) {
    let sql = `
        UPDATE
            user
        SET
            password='${newPassword}'
        where
            id='${id}'
        `;
    return sql;
}

let changeUserAvatar = function (id, imgpath) {
    let sql = `
        update
            user
        set
            imgpath='${imgpath}'
        where
            id=${id};
    `;
    return sql;
}

//获取文章（分页）
let getArticleListByPage = function (page, pageSize) {
    let sql = `
        select
            article.id,article.title,article.time,article.category,article.tags,article.summary
        from
            article
        where
            article.isdelete=0
        order by
            article.time desc ,article.id desc
        LIMIT 
            ${page * pageSize},${pageSize}

        `;
    return sql;
}

//获取文章数量
let getArticleNum = function () {
    let sql = `
        select
            count(*) as number
        from
            article
        where
            article.isdelete=0
        `;
    return sql;
}

//获取详情
let getArticleDetail = function (id) {
    let sql = `
        select
            *
        from
            article
        where
            article.id='${id}' and isdelete=0
        `;
    return sql;
}

//根据标签获取文章列表
let getArticleListByTag = function (tag) {
    let sql = `
        select
            article.id,article.title,article.time,article.category,article.tags,article.summary
        from
            article
        where
            isdelete=0 and article.tags like '%${tag}%'
        `;
    return sql;
}

//更新文章
let updateArticle = function (passage) {
    // let time = new Date()
    // time = util.dateFormat(time, "yyyy-MM-dd HH:mm:ss")
    console.log("-----test------")
    console.log(passage.content)
    let sql = `
        update
            article
        set
            title='${passage.title}',
            time=NOW(),
            category='${passage.category}',
            tags='${passage.tags}',
            summary='${passage.summary}',
            content= ?
        where
            id='${passage.id}';
        `;

    console.log(sql)
    return sql;
}

//创建一篇文章
let addNewArticle = function (passage) {
    // let time = new Date()
    // time = util.dateFormat(time, "yyyy-MM-dd HH:mm:ss")
    console.log("-----test------")
    console.log(passage.content)
    let sql = `
        insert into
            article (title,time,category,tags,summary,content)
        values
            ('${passage.title}',NOW(),'${passage.category}','${passage.tags}','${passage.summary}','${passage.content}');
        `;
    return sql;
}

//删除文章
let deleteAritle = function (id) {
    let sql = `
        UPDATE
            article
        SET
            isdelete=1
        where
            id='${id}'
    `;
    return sql;
}

//TODO
//TODO根据搜索关键词获取文章列表
let searchPassageFront = function (str) {
    let sql = `
        select
            article.id,article.title,article.time,article.category,article.tags,article.summary
        from
            article
        where
            article.state='发表' and (article.title like '%${str}%'  or article.summary like '%${str}%' or article.content like '%${str}%')
        `;
    return sql;
}

//发表评论
let addComment = function (comment) {
    let sql = `
        insert into 
            comments (uid,articleId,parentId,content,time) 
        values 
            (${comment.uid},${comment.articleId},${comment.parentId},'${comment.content}',NOW());
        `;
    return sql;
}
//获取单篇文章的评论
// let getCommentsById = function (id) {
//     let sql = `
//         select
//             *
//         from
//             comments
//         where
//             articleId=${id}
//         order by
//             comments.time desc
//         `;
//     return sql;
// }
let getCommentsById = function (id) {
    let sql = `
        select
            comments.id,parentId,name,time,content,uid,imgpath
        from
            comments,user
        where
            articleId=${id} and comments.uid=user.id and comments.isdelete=0
        order by
            comments.time desc
        `;
    return sql;
}

let getCommentByCommentId = function (id) {
    let sql = `
        select
            comments.id,parentId,name,time,content,uid,imgpath
        from
            comments,user
        where
            comments.id=${id} and comments.uid=user.id and comments.isdelete=0
        order by
            comments.time desc
        `;
    return sql;
}

let deleteComment = function (id) {
    let sql = `
        UPDATE
            comments
        SET
            isdelete=1
        where
            id='${id}'
    `;
    return sql;
}

module.exports = {
    Query,

    getUserInfoList,
    getUserDetailByName,
    getUserDetailById,
    regNewUser,
    deleteUser,
    changeUserPassword,
    changeUserAvatar,

    getArticleListByPage,
    getArticleListByTag,
    getArticleNum,
    getArticleDetail,
    addNewArticle,
    updateArticle,
    deleteAritle,

    getCommentsById,
    getCommentByCommentId,
    addComment,
    deleteComment,


}